{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Chapter 2\n",
    "## Loading Data\n",
    "\n",
    "### 2.0 Introduction\n",
    "The first step in any machine learning endeavor is to get the raw data into our system. The raw data might be a logfile, dataset file, or database. Furthermore, often we will want to retrieve data from multiple sources. The recipies in this chapter look at methods of loading data from a variety of sources, including CSV files and SQL databases. We also cover methods of generating simulated data with desirable properties for experimentation. Finally, while there are many ways to load data in the Python ecosystem, we will focus on using the pandas library's extensive set of methods for loading external data, and using scikit-learn--an open source machine learning library in Python--for generating simulated data.\n",
    "\n",
    "### 2.1 Loading a Sample Dataset\n",
    "#### Problem\n",
    "You want to load a prexisting sample dataset\n",
    "\n",
    "#### Solution\n",
    "scikit-learn comes with a number of popular datasets for you to use:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 0.,  0.,  5., 13.,  9.,  1.,  0.,  0.,  0.,  0., 13., 15., 10.,\n",
       "       15.,  5.,  0.,  0.,  3., 15.,  2.,  0., 11.,  8.,  0.,  0.,  4.,\n",
       "       12.,  0.,  0.,  8.,  8.,  0.,  0.,  5.,  8.,  0.,  0.,  9.,  8.,\n",
       "        0.,  0.,  4., 11.,  0.,  1., 12.,  7.,  0.,  0.,  2., 14.,  5.,\n",
       "       10., 12.,  0.,  0.,  0.,  0.,  6., 13., 10.,  0.,  0.,  0.])"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load scikit-learn's datasets\n",
    "from sklearn import datasets\n",
    "\n",
    "# load digits dataset\n",
    "digits = datasets.load_digits()\n",
    "\n",
    "# create features matrix\n",
    "features = digits.data\n",
    "\n",
    "# create target vector\n",
    "target = digits.target\n",
    "\n",
    "# view first observation\n",
    "features[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Discussion\n",
    "Often we do not want to go through the work of loading, transforming and cleaning a real-world dataset before we can explore some machine learning algorithm or method. Luckily, scikit-learn comes with some common datasets we can quickly load. These datasets are often called \"toy\" datasets because they are far smaller and cleaner than a dataset we would see in the real world. Some popular sample datasets in scikit-learn are:\n",
    "\n",
    "`load_boston`\n",
    "* Contains 503 observations on Boston housing prices. It is a good dataset for exploring regression algorithms.\n",
    "    \n",
    "`load_iris`\n",
    "* Contains 150 observations on the measurements of Iris flowers. It is a good dataset for exploring classification algorithms\n",
    "\n",
    "`load_digits`\n",
    "* Cotnains 1,797 observations from images of handwritten digits. It is a good dataset for teaching image classification\n",
    "\n",
    "#### See Also\n",
    "* scikit-learn toy datasets (http://scikit-learn.org/stable/datasets/index.html#toy-datasets)\n",
    "* The Digit Dataset (http://scikit-learn.org/stable/auto_examples/datasets/plot_digits_last_image.html)\n",
    "\n",
    "### 2.2 Creating a Simulated Dataset\n",
    "#### Problem\n",
    "You need to generate a dataset of simulated data\n",
    "\n",
    "#### Solution\n",
    "scikit-learn offers any methods for creating simulated data. Of those, three methods are particularly useful\n",
    "\n",
    "When we want a dataset designed to be used with linear regression, `make_regression` is a good choice:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Feature Matrix \n",
      " [[ 1.29322588 -0.61736206 -0.11044703]\n",
      " [-2.793085    0.36633201  1.93752881]\n",
      " [ 0.80186103 -0.18656977  0.0465673 ]]\n",
      "Target Vector \n",
      " [-10.37865986  25.5124503   19.67705609]\n"
     ]
    }
   ],
   "source": [
    "# load library\n",
    "from sklearn.datasets import make_regression\n",
    "\n",
    "# generate features matrix, target vector, and the true coefficients\n",
    "features, target, coefficients = make_regression(n_samples = 100,\n",
    "                                                 n_features = 3,\n",
    "                                                 n_informative = 3,\n",
    "                                                 n_targets = 1,\n",
    "                                                 noise = 0.0,\n",
    "                                                 coef = True,\n",
    "                                                 random_state = 1)\n",
    "\n",
    "# view feature matrix and target vector\n",
    "print(\"Feature Matrix \\n {}\".format(features[:3]))\n",
    "print(\"Target Vector \\n {}\".format(target[:3]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we are interested in creating a simulated dataset for classification, we can use `make_classification`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Feature matrix\n",
      " [[ 1.06354768 -1.42632219  1.02163151]\n",
      " [ 0.23156977  1.49535261  0.33251578]\n",
      " [ 0.15972951  0.83533515 -0.40869554]]\n",
      "Target vector\n",
      " [1 0 0]\n"
     ]
    }
   ],
   "source": [
    "# load library\n",
    "from sklearn.datasets import make_classification\n",
    "\n",
    "# generate features matrix and target vector\n",
    "\n",
    "features, target = make_classification(n_samples = 100,\n",
    "                                       n_features = 3,\n",
    "                                       n_informative = 3,\n",
    "                                       n_redundant = 0,\n",
    "                                       n_classes = 2,\n",
    "                                       weights = [.25, .75],\n",
    "                                       random_state = 1)\n",
    "\n",
    "# view feature matrix and target vector\n",
    "print(\"Feature matrix\\n {}\".format(features[:3]))\n",
    "print(\"Target vector\\n {}\".format(target[:3]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, if we want a dataset designed to work well with clustering techniques, scikit-learn offers make_blobs:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Feature Matrix\n",
      " [[ -1.22685609   3.25572052]\n",
      " [ -9.57463218  -4.38310652]\n",
      " [-10.71976941  -4.20558148]]\n",
      "Target Vector\n",
      " [0 1 1]\n"
     ]
    }
   ],
   "source": [
    "# load library\n",
    "from sklearn.datasets import make_blobs\n",
    "\n",
    "# generate feature_matrix and target vector\n",
    "features, target = make_blobs(n_samples = 100,\n",
    "                              n_features = 2,\n",
    "                              centers = 3,\n",
    "                              cluster_std = 0.5,\n",
    "                              shuffle = True,\n",
    "                              random_state = 1)\n",
    "\n",
    "# view feature matrix and target vector\n",
    "print(\"Feature Matrix\\n {}\".format(features[:3]))\n",
    "print(\"Target Vector\\n {}\".format(target[:3]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Discussion\n",
    "As might be apparent from the solutions, make regression returns a feature matrix of flaot values and a target vector of float values, while make_classification and make_blobs return a feature matrix of float values and a target vector of integers representing membership in a class.\n",
    "\n",
    "scikit-learn's simulated datasets offer extensive options to control the type of data generated.\n",
    "\n",
    "In `make_regression` and `make_classification`, `n_informative` determines the number of features that are used to generate the target vector. If n`_informative` is less than the totla number of features (`n_features`), the resulting dataset will have redundant features that cna be identified through feature selection techniques\n",
    "\n",
    "In addition, `make_classification` contains a `weights` parameter that allows us to simulate datasets with imbalanced classes. For example, `weights = [.25, .75]` would return a dataset with 25% of observations belonging to one class and 75% to the other\n",
    "\n",
    "For `make_blobs`, the centers parameter determines the number of clusters generated. Using the `matplotlib` visualization library we can visualize the clusters generated by `make_blobs`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXwAAAD8CAYAAAB0IB+mAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMi4zLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvIxREBQAAIABJREFUeJzt3Xl8XFX5x/HPMzOZSdJ0X4U2bREoQikFwiJL2cqiIIsgIiIgP6wgawUXREBcAHFBREUrLigKsu8IrWwia1oKpdCWtfuS7k2TyWzP749JS9JMkkmTmzSZ7/v14mXnzp17novlO2fOPfdcc3dERKTnC3V1ASIi0jkU+CIiBUKBLyJSIBT4IiIFQoEvIlIgFPgiIgVCgS8iUiAU+CIiBUKBLyJSICJBN2Bm/YDbgLGAA+e4+0u59h00aJCPGjUq6JJERHqU6dOnr3T3wa3tF3jgAzcD/3b3U8wsCpQ2t+OoUaOorKzshJJERHoOM5ufz36BBr6Z9QEmAGcDuHsCSATZpoiI5Bb0GP4OQBXwFzN73cxuM7NeDXcws0lmVmlmlVVVVQGXIyJSuIIO/AiwF3Cru+8JbAS+23AHd5/i7hXuXjF4cKtDUCIispWCDvxFwCJ3f6X+9b1kvwBERKSTBRr47r4MWGhmY+o3HQG8HWSbIiJbK5VMsXF9DT31OSGdMUvnIuAf9TN0PgC+2gltiojkLRFP8LvJf2Hq7c+RTmUYUj6IS279GnsfuUdXl9ahAr/xyt1n1o/Rj3P3E919TdBtioi0xQ1n3sLU258nEU+STqVZ+sFyrjnpRt6b+WFXl9ahdKetiPQ4H7w5n6fvfIH3Xm89sFctXcPLj0wnEW88YzwRT3LXTx8MqsQu0RlDOiIinSJeU8f3P3c9c155j1DYyKSdnfYezXWPfY+SspKcn1k+v4pocRHJumSj7Z5xFr6zuNU23Z2Fc5eQTqYYudsIQqFttx+twBeRHuO2797BOy/NIxH/OLznvvo+t37zdr455bycnxkxZrsmYQ8QjoTYZb+dWmzvo9kL+cHnf8bKxasJhYyS3sVceedkxk3YtX0nEpBt96tIRKSNnrr92UZhD5CsS/KfO55vduZN7/5lfO68o4iVxjZvM4NoSYzTvnNis20l4gkuO+waFr+7lLqaOmqr46xeupYrj72O1cu2zUuV6uGLSLf13usfct/Nj7HswxXsdcTuJGpzr9ySyNGDb2jSz89k6Ogh3PfLR9mwuprdDtqFSTd+hU/sMLTZz7z86PScvwwy6QxT//48X/zWCW07mU6gwBeRbumFB17hhjN+TbIuSSbjzHvtPdLpTM59h4wYhJk1e6xQKMRJF32Wky76bKvtVq/dyJxX32Pua++TTqabvJ+IJ1m5eHX+J9KJFPgi0u2k02lu+vofqGvQo99yKKehumZ6/lt647nZ3Dr5r3w4awF9Bvbmi98+gZMnH7f5y+KeXzzMX6+6i0g0QjqZztnDLykrZs/DxjbalkqmCIVDXX5BV4EvIt3O0veXNzt8k1MeN86+9GglPz71l5u/ONauWMdfr/4X61dt4JyfnM7rT8/i9mvuJhFPNvpyMbPN1wdiJVFG7jaC/Y7NriAzb/r73Hz+FN6d8SGRoghHnjmB82/6KsUNrhd0JgW+iHQ7vfqWkk41HU7JJRKNMOEL+wOwfvUGfnHurVT+eyaJeJJho4cwbPQQ3nx2NplM02+Fupo67v/VY5x+5ck89JsnqKupa7JPuCjM9jt9gnAkxMQzJnD8N44mHAmzfH4Vlx/2A2qr40D24vG0vz/P8o+quOHJq9px9ltPgS8i3U7/of3Y7cBdmPXfdxqNo0dLijALAU5dTYKSsmL6DenL2T88jeXzq/i/3SY3Cu1lH65g2YcrWmwrFA6xcvFq1lZtyPl+KpniC5cfz9FnHdpo+wO3PE6yLtVoWyKe5K0X5rBo3hKG77xd2066A2hapoh0S9+/azLbf3IYoXCIcCREpCjMCRd+ljsX/p5zbziDEy/6DBf/7mvcNvsm+gzsze8u/UvOHnprMukMA7frz4En7kOkKNx0B4e7bri/yeYP3phPKplqsj0SjbBo3tI219ER1MMXkW7p/l8/zoqFK8lkMuAQK42xaO5ievUt5cQLPwNk74LddMG18qmZbW4jVhrjhAuOpqRXMcd9/Uj+8v07c+5XtWAVSz9Y3mga5y777shbL7zTpJefrEsycrfhba6lI6iHLyLdzvL5Vdz784eJb6zbfEG2rqaO159+ixnTZvHqE6/z1V0u5qjwqZw8+Kv868YHiUTb1r+NFhexzzHjWVu1njuvv5/a6jjDRg/Jua+FrElv/oQLP0O0OErD2aDRkij7HbsXnxjd/Pz+IKmHLyLdzvSpb+ac4hivjvPwrU8yY+ob1NVkZ/GsX1XN3394L6PHlTPvtffJNDNXv6GS3sVEiiK8+sTrJGoTFMUi3Hn9A0z8ygSWz1/ZZIZQ7wFlTcbkB36iP7e8fB23Tr6dN559i+JexRx33pF85eovtOPM20eBLyLdTmnvYgg1vZEqXBTmvRkfbA77Tepq6vho1gJG7jqcD99asPlXQVEswph9duSdl+eRTmUoimUXUavdEG/0+WRdimRdilcem8Go3UawYM5i4tVxoiVRwuEQV945OeeNXSPGbM91j3+v4068nRT4ItLtjNl3R+Ib4022G1CzoTbnZ+pqEyx5fzl49oshHA7x40evYM/Dd8fd2bBmA6cMObfFdlcsWMkNT32fBW8v5s3nZjN4xCAmfmUC/Qb37YjTCpwCX0S6nYd++2/C4XDTufghI5VoOjMGsssdb5qlk06mSSfT/Pi0mzjnui/x4M1PsOyjFXiOufhben3aLAZtP5BQJEwoHGpxyYaGMpkM7k44nGOmTyexoJ/daGZhoBJY7O7HtbRvRUWFV1ZWBlqPiHR/X9v9m3w0e2GT7cVlMZJ1qZxr3OQSCmenc7a0LMOWBm4/gI3rahoN6fx06tV8qpmllNdWrePm8//ISw9X4u6MP2wsl/5hUodeuDWz6e5e0dp+nTFL5xLgnU5oR0QKxIBP9M+5PZ1It2m9mkw606awDxeFWVe1nnj93bOJ2gS11XGu+9JNOZdfTqfTTD74Kl56uJJ0Kk0mnWHm07O4aP/vUVude+gpSIEGvpkNB44FbguyHREpLF+4/PhG69cDRIrCfGr/lh9Y0h5FxUWEwqGcQ0bLF6zk3LGTufvnDxFvcHPXjKlvsmrpmkZDT5n6oaVn7noxsFqbE3QP/1fAt4HW50GJiOSp4qg9OPeGL1PcK0ZpnxKixUV86tNjuOzPF5DJc42dlljICEVC2aUZhvZl0PABnDL5OLZrZn18zzgL3lnM3665m29OuGrznPxF85aSSjStJ76xjvmzF7S7zrYK7KKtmR0HrHD36WZ2aAv7TQImAZSXlwdVjoj0MCde+BmOOedw5s9eSL8hfRk6cjAb1lRj4RDkMde+ObGSKJOnnMcOe4xk1G4jGl2U7T+sH3+64h9Npn1uUlebYOG8pbxw/ysc+sUDGTV2BJGicJNllEvKitlxzx344M353PHje/lg5keMGjuCL3//FHbaa4etrr01QfbwDwSON7OPgLuAw83sji13cvcp7l7h7hWDBw8OsBwR6WmKS2OM2WdHho7MZkfv/mXssHs5eU6caaL/sH5cdc9lHPHlgxk9trzJDJzPnXcU4w8bS6w0RjiSOz7j1XGmT3sTgD0O3Y3tdhzW6C7fcCRMr369GFw+kIsPuJIX7nuFxe8t48WHKpk84SreeHb21hWfh8AC392vcPfh7j4KOA142t3PCKo9ERGA7/z9YnoPKCMUblu8HfT5fblr0R/Y77N7NbtPpCjCjx+5gl888wOOPvswosXRJvsURSMM2n4AkH2S1qZ9S3uXECuNcfAp+/ObV67ntu/8g7qaus0Xe92zK3z+5uI/tanutgh8WiZA/ZDO5ZqWKSKdoba6lnt+8Qj/vO7+RlM0YyVR9jpqHAveXsTid5cB2amZE8+cwCW//VrOAG9OKpni9PLzWLtiHQ1jNFYa489v38SQ8pZHLI6Jntbsmv5Ppv7VptlG+U7L7JQbr9z9WeDZzmhLRKSkrIQzrzmVPQ7ZjVsuvI0FcxYTK4ly7KQj+b/rT6coWkQ6nWb10rWU9e9FSa/iNrcRKYrw82eu5ZoTb6Rq0SpCISNaEuW7f7+YouIo0+54PrsA22f2zHn83gN6sXbF+ibbe/UtDexRiJ3Sw8+XevgiEoRkIkmkKJL3XbFt4e4snLuERDzB6N3LeeTWp/jjt/9OOBIGMzyT4doHvs1eE8c1+txdP32AO350X6M1+mOlUb5w2fGcde0X21TDNtXDFxHpSkXRog45jrvz/D0v8eBvnmDjuhoOPnl/Pn/psZTvsj0AH761gNu+c0f9zVwfz8y55qQbuXvpHykpK9m87dRvncDa5et45PdPEYlGSCVSHHnmoZxx9SkdUmsuCnwRkTz94Vt/47E/TM2uww8sfncpT//zv9w642cUl8aY+rdnSea4MctCxsuPzuCw0w7cvC0UCnHeL8/mKz84leUfVTGkfBBl/XoFWr8egCIikoeVi1fx8O+e3Bz2kH1GbdWi1Uy9/VkA4jWJnOvte8apq809d79Xn1J2GDcy8LAHBb6ISF7eefldinI8Nauupo7X/p19fOJBJ+1Hca9Yk30y6QwVR+8ReI2tUeCLiOSh/7B+OZdPDoVDDC4fBMCeh4/l08dXbA59Cxmxkihn/fA0Bm03oFPrzUVj+CIiedjtgDH0G9qXupo6Mg2CvygW4fhvHA2AmXHFHZcwfeqbPH/vSxSXRjnyzEMDXS6hLTQtU0QkT8vnV3HNSTeyaO4SQpEQkUiYy/98AQecsE+X1qVpmSIiHWzoyMH8fsbPWPL+Mmo21DJ6bHl2vn03ocAXEWmj7T45rKtL2Cq6aCsiUiAU+CIiBUKBLyJSIBT4IiIFQoEvIlIgFPgiIgVCgS8iUiAU+CIiBSLQwDezEWb2jJm9Y2azzeySINsTEZHmBX2nbQq4zN1nmFlvYLqZTXX3twNuV0REthBoD9/dl7r7jPo/bwDeAbYPsk0REcmt08bwzWwUsCfwyhbbJ5lZpZlVVlVVdVY5IiIFp1MC38zKgPuAS919fcP33H2Ku1e4e8XgwYM7oxwRkYIUeOCbWRHZsP+Hu98fdHsiIpJb0LN0DPgT8I67/zLItkREpGVB9/APBL4CHG5mM+v/+WzAbYqISA6BTst09xcAC7INERHJj+60FREpEAp8EZECocAXESkQCnwRkQKhwBcRKRAKfBGRAqHAFxEpEAp8EZECocAXESkQCnwRkQKhwBcRKRAKfBGRAqHAFxEpEAp8EZECocAXESkQCnwRkQKhwBcRKRCd8RDzY8xsrpm9Z2bfDbo9ERHJLeiHmIeB3wKfAXYFvmRmuwbZpoiI5BZ0D39f4D13/8DdE8BdwAkBtykiIjkEHfjbAwsbvF5Uv01ERDpZ0IFvObZ5ox3MJplZpZlVVlVVBVyOiEjhCjrwFwEjGrweDixpuIO7T3H3CnevGDx4cMDliIgUrqAD/zVgJzMbbWZR4DTg4YDbFBGRHCJBHtzdU2Z2IfAkEAb+7O6zg2xTRERyCzTwAdz9ceDxoNsREZGW6U5bEZECocAXESkQCnwRkQKhwBcRKRAKfBGRAqHAFxEpEAp8EZECocAXESkQCnwRkQKhwBcRKRAKfBGRAqHAFxEpEAp8EZECocAXESkQCnwRkQKhwBcRKRAKfBGRAqHAFxEpEIEFvpn9zMzmmNmbZvaAmfULqi0REWldkD38qcBYdx8HzAOuCLAtERFpRWCB7+5PuXuq/uXLwPCg2hIRkdZ11hj+OcATndSWiIjkEGnPh81sGjAsx1tXuvtD9ftcCaSAfzRzjEnAJIDy8vL2lCMiIi1oV+C7+8SW3jezs4DjgCPc3Zs5xhRgCkBFRUXOfUREpP3aFfgtMbNjgO8Ah7h7TVDtiIhIfoIcw/8N0BuYamYzzez3AbYlIiKtCKyH7+47BnVsERFpO91pKyJSIBT4IiIFQoEvIlIgFPgiIgUisIu20rJXFy/i+heeY+6qlQztVcbF++7PSZ/aravLEpEeTIHfBaYvXczZD91HPJVdamj+urV8/5lprE/UcdYee3VxdSLSU2lIpwv87H8vbA77TWpTKW56+UVSmUwXVSUiPZ0CvwvMW7Uy5/a6VIo1tbWdXI2IFIoeEfjuzvq6OMl0uqtLycuIvn1zbg+HQvQtLu7kakSkUHT7Mfyn3n+XHzz3NCtraoiEQpy661i+d/ChRMPhrTqeu/On16fz++mvsqa2lk8OGMBVBx/GwSNHNdpv0fp1TF+6hEGlpey//QjCofy/Oy/d/wAuePyRRsM6JZEI54zfe6vrFhFpjTWziGWXqKio8MrKyrz3f23JIs568L5GwVkcifC5nXfhpxOP3qoabnr5RW6b8Rq1Wxzz9hNPZp/thuPuXP3sf7j37beIhEKA0TcW458nn0p53/yf4vjIvDlc/9/nqKrZSEmkiHP32psL9/00IbOtqltECpeZTXf3ilb3686Bf9aD9/HfBR812R4Lh3nl3PPoE/t4eGR5dTV3z57Fog3r2Hf7ERy30xhikcY/cOpSKfaa8jtqU8kmxxw3ZBgPnvZlHpr7Dt/7z1ONvhAAdhwwgKfO+GretUP210Q8lSIWiSjoRWSr5Rv43XpIZ/7aNTm3R0Jhlldv3Bz4lUuy0yBTmQyJdJrH3p3HrZWvcP+pX6ZPLLb5cy8uXEAyk/s6wJsrlnHOQ/ezNl7bJOwB3lu9mkfmzeFzO++Sd/1mRklRUd77i4i0R7e+aDtu6LCcPeOMZxjepw+Q7UVPfvJxapJJEvUXdWuSSRatX88fpr+6eZ/vTnuSCx5/uMVpkS8tWsCCdeuaff+Sfz/GZ/95Ow/OeYdt6ZeTiAh088C/aN9PU7zFsExJJMLX995nc8954bp1LKve0OSziXSaR+fNBeCZjz7kkXlziLcyy6cunaYm2XS4p6E5K1dy5dNPce1zT+d8f1VNDX+cUclVT0/jwTnvULfFrwV3Z1VNTavtiIi0Vbce0tlp4EDuOeU0rv/f88xctpSBJaWcX7EvX9h1LAAfrFnNxU88SrqZ3nY0nP2+u21GZc5hmlxCZhjQUv+9NpXirtmz+Pre+/KJ3r03b39rxXJOv+9ukpkMdekU98+ZzS2vvsQDXzydPrFiXlq4gO/+5ymWb6zG3Zm4wye5/oijGw07iYhsrW4d+ACfGjyEv514SpPty6o3cNK//sGGRKLZz7rDypqNvLZkUV5thcw4fPQOvLhwAavjLd8gFQ2FeX3Z0kaBP/nJx6lOflxPbSrFh2vX8LVHHuQnh03k3EceaPTF858PP2DSow9y18lfzKs+EZGWdOshnZb8deYM4qmWh2g+WruG0++7m6I8576HML4ybnyT2T25JDJphvTqtfl11caNLFyfe/z/tSWLueLpqZuvMWw+RjrNm8uX8f7qVXnVJyLSksAD38wuNzM3s0FBt9XQzOXLmp1xs0mG7MJlLY7PNJDyDF9/7CHSeax3k0ynKWpwM1Y4ZC1eyH1j+bKcQ09FoRCLNzS9BiEi0laBBr6ZjQCOBBYE2U4uOw8YSCSPue2RUIiU579g2dp4PO/97377rc1/HlBSyrihw5rdN5XJNPqC2CSRTjNmYKd+V4pIDxV0D/8m4Nvk3YfuOF8dvxfhUD5DNcYXd92dkgY3P4Vb+aJYH4+3elQHqre4fvCrY44lbLn/lY/o05de0SghPm67JBLhpF12ZWhZWavtiYi0JrCLtmZ2PLDY3d+wFgLUzCYBkwDKy8s7pO1UJsP1LzzPlt8zETNSDYZNoqEQuwwaxI8On8jJu+7GPW/PojaZok8sxj/fnElz83ac7N28dS1M4ywtKuKA4eXc8MLzzFy+lDEDB3HO+L259djPccHjj5BsMCxUHIlw9YTD2GXQYH7+0gs8P/8jekejnD1+L87cY892/JsQEflYu5ZWMLNpQK5xiiuB7wFHufs6M/sIqHD33OsC12vr0grNufftt7jm2aebLJHQr7iYMQMH8dqSxURCIU4YswtXTTicsmi00X4zly3l5Lv/2eLPkjEDB/HBmtXEIhESqRSJTIaQGRl3SouK2G3wEN6pqiKRSZNIpwmbEQtH+PtJp7AxmeTnL77Ah2tXM6pffy7/9EFNFmcTEclXpyyt4O4Tm2l8d2A0sKl3PxyYYWb7uvuy9rSZj3vefivnejjJdIYrDjqEsUOGYtk6c37+6memtToGNW/VSmKRCJP22oc9hg6jLBrlgTlvsyGR4DM77sSds95oNAUz7U5NKsmFTzzC/875OgeVj2zHGYqItF0gQzruPgsYsul1vj38Dmu/lXdaWqhsYyLBnJWtl+lAPJXisXfncuG++wOw5ye2AyDjzjcefyTn55ZWV/OfD97niB0+2WobIiIdqUfOwz9117GU5JgrXxLJDrW0JBIKQRsWrnxv9SpqkknWxmtZWVMDwGPvziXTwlDZlOmv5d+AiEgH6ZQ7bd19VGe0s8lJu+zKk++/y4sLF1KXShGLhDEzfnfs8a0+qCQWiXDIyNE8N//DvJ4vGzLjzAfuYdaK5ZgZo/r2a3JNYEtLcqztIyIStG6/tEIu4VCIKcedSOXSxby6eBH9i0s4dqcxeT8+8IYjjuJL99/N0g3ryWScukw6Z489Gg4TMmt009S81atanda5d/3Qj4hIZ+qRgQ/ZC7L7bDecfbYb3ubPDiwt5ckvn8WrixexYP06PjVoMANKSjjt3n+xaMP6j/crKWFdPN7kDtkQRihkjaZeblIaiXDxfp9u+wmJiLRTjw389jIz9hs+gv0YAcBVz0xjZW1No32qampyDvcnPcOoPv1YvrGaulQax3Fgv+2G8+PDJ7JD/wHBn4CIyBYU+HmoSSa59+23mtxolcpkcgZ+r6IiLt3/AEb268+LC+fTr7iEz+64c95DSiIiQVDg52FVTU39kggtL8a2ycCSUo7+5E7EIhH2aGH9HBGRztQjp2V2tKFlZYRC+c3VDJF9MEs+SyiLiHQmBX4eouEw39z/wEZz+zfF/5ZzdzLA8/M/ItnK4xJFRDqbAj9PZ4/fixsnHsOYgYPoV1zMhJGjct7cBdllFFq68UpEpCto3KENjt15DMfuPGbz60v+/RiPvzu30bRMA8YPHaYhHRHZ5qiH3w7fO+gQBpaUUhIpArLLHPeOxbj+iKO7uDIRkabUDW2HoWVlTDvzHB6a8zZvLF/GTgMGcsquY+lfUtLVpYmINKHAb6eyaJQvjxvPl7u6EBGRVmhIR0SkQCjwRUQKhAJfRKRAKPBFRAqEAl9EpEAEGvhmdpGZzTWz2WZ2Y5BtiYhIywKblmlmhwEnAOPcvc7MWn6YrIiIBCrIHv75wA3uXgfg7isCbEtERFoRZODvDBxsZq+Y2XNmtk+AbYmISCvaNaRjZtOAXE/4uLL+2P2B/YF9gLvNbAf3xstImtkkYBJAeXl5e8oREZEWtCvw3X1ic++Z2fnA/fUB/6qZZYBBQNUWx5gCTAGoqKjQmsIiIgEJckjnQeBwADPbGYgCKwNsT0REWhDk4ml/Bv5sZm8BCeCsLYdzRESk8wQW+O6eAM4I6vgiItI2utNWRKRAKPBFRAqEAl9EpEAo8EVECoQCX0SkQOiZttLteXoFxB/HMxuw2MFQtAdm1tVliWxzFPjSrXn8GXztJUAGSOIbb4PiI6HvjZjpB6xIQ/ovQrot9zi+bjIQJ3tvnwO1UDcN6p7p2uJEtkEKfOm+Eq+S86+w1+C1D3Z6OSLbOgW+dGMtjdNrDF9kSwp86b6i+zbzRglWclKnliLSHSjwZZvn6Soy1VPIrLsWjz+BexIAsxjW79dASf0/EaAYSj4HsUO7rmCRbZRm6cg2zRMz8DXngKeBOjz+AIRvhQF3YaFSLHYQDHkO4v8Gr4boQVjRLq0fN7MGEi+DlUD0AMyiwZ+MSBdT4Ms2y93xtZPBaxpsrIHUB/iqU/HMsmxgl56O9foaZvn9dc5s/Dts+ClYEdmx/hD0vw2Ljg/kPES2FQp8CVSm9lGo/hWkl0J4JJSdj3kNkILYYVh4u+Y/nJ4PmbU53khAel72j74eqm/Fk3Ox/r9qtR5PzoYNP8sewxMfb19zLgx5UT196dEU+BKYTM19sP5asvPkgfR7sO4ynCjZy0c34GWXEir7v82fca/F0+uxxH/x1DwglUdLcah7ikxyPqGikS3u6TX3kJ2z36RaqPsfFB+W17mJdEcKfAmEu0P1L9gc9o00CNzqm/HYBAj1w9d9FxIvAmmcENm7Z/OdXpmCDTfCgN+2Ulh1/XGbvAG+Mc+2RLonzdKRdnFP495MqGdW53GEJF77ML769M1hn7UplDc9FTMChFs+VOJZMhvvwGvux3MOBYEVHwVW2vQNT0HsgDzqFem+Agt8MxtvZi+b2UwzqzSz5iZNSzfkniCz/sf48j3x5ePJVB2F173YYI8oWJ88jpSpH6tfycdhn0ua3D3zhpKw4UZ8/Q/xFRPI1D7RdJfYEVC0d4PQDwHF0HsyFhqQR70i3VeQPfwbgWvdfTxwdf1r6SF83RVQczfZIZsMpD/C15yHJ98GyK5WWXYB2fnxLYlBpLx+2mWLLfJxb78lcaAm+7/rvo1v8SvDLIz1n4L1/SkUHwslX8AG3kGo1zl5HFukewtyDN+BTV28vsCSANuSTuTpVRB/kqYXP+vw6t9j/X8NgJWemR2Lr/4N+Fqwsvoplka2tx7N9ri9iPzCvK1CEJ8KpV9stNUsDMVHY8VHB9CmyLYryMC/FHjSzH5O9pdEzgFSM5sETAIoLy8PsBzpMJnFYNFG0xqzHFLvb35lZlivr+ClZwApzIrw1Pt47WOQqf/SqHucYMIeIIN7YqtX1XFPQOK17ItoBWaxDqtMpCu0K/DNbBowLMdbVwJHAJPd/T4zOxX4EzBxyx3dfQowBaCioiKo//KlI4VHQv3yBo2FILp7k63Zh5EUZf8c+SSUXYRXHQ6ez0Xd9qiD6lvIWJTQFr381njiLmaQAAAIW0lEQVTd//C1FzXcAv1+hcUO6dgSRTpRuwLf3ZsE+CZm9jfgkvqX9wC3tact2XZYqC9eelr9GH5tgzeKsV7ntX6A1CzIrAisvkZ8LWy4jgwlhEqPz+8jmbX42m+A1zbevuYiGPw0Fh4URKUigQvyou0SYFN36HDg3QDbkk5mva+A3pdCaBhQDNFPYwPuxCKjWv9wZn1AVTVzl6zXwsZf53+Y+L+bGWVyiD+2NYWJbBOCHMP/GnCzZRc4iVM/Ti89g1kI6/VV6PXVtn+4aA86dtzeoGgviB0L1T/Kfez00vwPl9kA5BqySoJv2MoaRbpeYD18d3/B3fd29z3cfT93nx5UW7Ltc3c8+SYefzK7/k2vyR105Aj0voLQwDsJlZ0BoaHN7DY6/0PGDiR3XygG0YO2pkiRbYKWVpDAeXoVvuZsSC8EQtnZPbFDyQ7B5FrXJl8hCA3CSs/4eFPvb8G6K2m8pEMx1vtbeR/VinbFS46F+OMNxvFLs+vsFO3RjnpFupYCXwLn6y6rn67ZYCG0uudpeWG0UqAGbACEBmXvxs1+kOw8/ghExmD9bm60LHKo5HO4xfANN0F6MURGY70vx2IHt6lm63MdxCbitfcDmewTtGIT62cciXRPCnwJVPZBI5U0Dfd4/Y1YCZrt5fe9BVLzYOMUskG/SRiiBxIaMCXnx6z4qOyaOe1gZlB8BFZ8RLuOI7It0eJpEiyvpdkVL60XFFU088EaiN8PtXfROOwBUpB4IXtjlIjkTYEvwQp9AnIuShaB2JFY2dezwZ+TQc6VOAFS+LofbH6+rYi0ToEvgTIzrO/1fPyQcYBiCA3Eyi6A6N7k/mtYWj9ufkgz7wPxR/ENN3RIne51ePwZPD4Vz1R3yDFFtjUaw5fAWewAGPQwXvOP7MXXon2x0lOxUO/sDv1+ja/5Btn580kgCsUTs78AisZll132NTSdXx+Hmnvw3t9u1zo3Xvdy9s7azRtSeJ8fESo9YauPKbItMvdtZ/maiooKr6ys7OoypAt4ZnX2DtfMOogdiBWNa/DeOnzFweR+elYMG/zMVi934JlqvOqgxg9KB6AYG/QoFtGCfrLtM7Pp7t7cBbHNNKQj2wQLDcBKT8fKzm8U9tn3+kJ0n2Y+2KuZawR5qvtPMzf9pvDah7b+uCLbIAW+dDpPziKz5iIyK48ns+4HeHpxq5+x3peRvQ7QcMZPMfT+Lmbt+GvsG8n9JK20llGQHkdj+NKpPP4MvvYSslMtHVLv4fFHYOB9LS68ZkW7wsB/4dU3Q3IWhLfHyi7AYhPaV1D0IHJ28a0Yi2kOvvQsCnzpNO6Or7+axmPxKfCN+IZfYP1vafHzVrQL1v/WDq3JIuV46dlQ87f6ujz7vNvoBIjqMczSsyjwpfNkVkFmTa43IPFqp5ezSajPZXjxwXjNfUACKz4OYodpGQXpcRT40nlCzd1gBYT6dV4dOVh0X0w9eunhdNFWOo1ZCRR/BthyznwJ9Dq3K0oSKSjq4Uunsj7X4l4NdS+AFWWfjVt6BlZySleXJtLjKfClU1moFOt/K55eBunl2eWLQ326uiyRgtCuIR0z+4KZzTazjJlVbPHeFWb2npnNNbOj21em9DQWHoZF91DYi3Si9vbw3wI+D/yh4UYz2xU4DdgN2A6YZmY7u3u6ne2JiMhWalcP393fcfe5Od46AbjL3evc/UPgPUBTIEREulBQs3S2BxY2eL2ofpuIiHSRVod0zGwaMCzHW1e6e3OrS+W6YyXnElVmNgmYBFBerpUJRUSC0mrgu/vErTjuImBEg9fDgSXNHH8KMAWyyyNvRVsiIpKHoKZlPgz808x+Sfai7U5Aq/fOT58+faWZzQ+opo42CFjZ1UV0kkI510I5Tyiccy2U8xyZz07tCnwzOwm4BRgMPGZmM939aHefbWZ3A28DKeCCfGbouPvg9tTTmcysMp8HDvQEhXKuhXKeUDjnWijnma92Bb67PwA80Mx7PwF+0p7ji4hIx9FaOiIiBUKBv/WmdHUBnahQzrVQzhMK51wL5Tzzsk09xFxERIKjHr6ISIFQ4LdBoS4WZ2bjzexlM5tpZpVm1qOXyTCzi+r/f5xtZjd2dT1BM7PLzczNbFBX1xIEM/uZmc0xszfN7AEz69qn7XQhBX7bbFos7vmGG7dYLO4Y4HdmFu788gJzI3Ctu48Hrq5/3SOZ2WFk14Ia5+67AT/v4pICZWYjgCOBBV1dS4CmAmPdfRwwD7iii+vpMgr8NijgxeIc2LSOcV+auWu6hzgfuMHd6wDcfUUX1xO0m4Bv08zSJz2Buz/l7qn6ly+TvfO/ICnwO0ZPXyzuUuBnZraQbI+3J/eQdgYONrNXzOw5M9unqwsKipkdDyx29ze6upZOdA7wRFcX0VX0xKstBL1Y3LaqpfMGjgAmu/t9ZnYq8Cdga9ZY2ia0cq4RoD+wP7APcLeZ7eDddDpbK+f6PeCozq0oGPn8d2tmV5K98/8fnVnbtkSBv4WgF4vbVrV03mb2N+CS+pf3ALd1SlEBaeVczwfurw/4V80sQ3Y9lqrOqq8jNXeuZrY7MBp4w8wg+3d2hpnt6+7LOrHEDtHaf7dmdhZwHHBEd/3y7gga0ukYDwOnmVnMzEaT52Jx3cgS4JD6Px8OvNuFtQTtQbLniJntDETpgYtvufssdx/i7qPcfRTZTste3THsW2NmxwDfAY5395qurqcrqYffBh29WFw38jXgZjOLAHHqn1/QQ/0Z+LOZvQUkgLMKuUfYQ/wGiAFT63/NvOzu53VtSV1Dd9qKiBQIDemIiBQIBb6ISIFQ4IuIFAgFvohIgVDgi4gUCAW+iEiBUOCLiBQIBb6ISIH4fzCMxFQPuMggAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# load library\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "# view scatterplot\n",
    "plt.scatter(features[:, 0], features[:, 1], c=target)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### See Also\n",
    "* `make_regression` documentation (http://scikit-learn.org/stable/modules/generated/sklearn.datasets.make_regression.html)\n",
    "* `make_classification` documentation (http://scikit-learn.org/stable/modules/generated/sklearn.datasets.make_classification.html)\n",
    "* `make_blobs` documetnation (http://scikit-learn.org/stable/modules/generated/sklearn.datasets.make_blobs.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3 Loading a CSV File\n",
    "#### Problem\n",
    "You need to import a comma-separated values (CSV) file.\n",
    "\n",
    "#### Solution\n",
    "Use the `pandas` library's `read_csv` to load a local or hosted CSV file:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cdatetime</th>\n",
       "      <th>address</th>\n",
       "      <th>district</th>\n",
       "      <th>beat</th>\n",
       "      <th>grid</th>\n",
       "      <th>crimedescr</th>\n",
       "      <th>ucr_ncic_code</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1/1/06 0:00</td>\n",
       "      <td>3108 OCCIDENTAL DR</td>\n",
       "      <td>3</td>\n",
       "      <td>3C</td>\n",
       "      <td>1115</td>\n",
       "      <td>10851(A)VC TAKE VEH W/O OWNER</td>\n",
       "      <td>2404</td>\n",
       "      <td>38.550420</td>\n",
       "      <td>-121.391416</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1/1/06 0:00</td>\n",
       "      <td>2082 EXPEDITION WAY</td>\n",
       "      <td>5</td>\n",
       "      <td>5A</td>\n",
       "      <td>1512</td>\n",
       "      <td>459 PC  BURGLARY RESIDENCE</td>\n",
       "      <td>2204</td>\n",
       "      <td>38.473501</td>\n",
       "      <td>-121.490186</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     cdatetime              address  district        beat  grid  \\\n",
       "0  1/1/06 0:00   3108 OCCIDENTAL DR         3  3C          1115   \n",
       "1  1/1/06 0:00  2082 EXPEDITION WAY         5  5A          1512   \n",
       "\n",
       "                      crimedescr  ucr_ncic_code   latitude   longitude  \n",
       "0  10851(A)VC TAKE VEH W/O OWNER           2404  38.550420 -121.391416  \n",
       "1     459 PC  BURGLARY RESIDENCE           2204  38.473501 -121.490186  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load library\n",
    "import pandas as pd\n",
    "\n",
    "# create url\n",
    "url = \"http://samplecsvs.s3.amazonaws.com/SacramentocrimeJanuary2006.csv\"\n",
    "\n",
    "# load data\n",
    "df = pd.read_csv(url)\n",
    "\n",
    "df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.4 Loading an Excel File\n",
    "#### Problem\n",
    "You need to import an Excel spreadsheet\n",
    "\n",
    "#### Solution\n",
    "Use the `pandas` library's `read_excel` to load an Excel spreadsheet:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Eldon Base for stackable storage shelf, platinum</td>\n",
       "      <td>Muhammed MacIntyre</td>\n",
       "      <td>3</td>\n",
       "      <td>-213.25</td>\n",
       "      <td>38.94</td>\n",
       "      <td>35.00</td>\n",
       "      <td>Nunavut</td>\n",
       "      <td>Storage &amp; Organization</td>\n",
       "      <td>0.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1.7 Cubic Foot Compact \"Cube\" Office Refrigera...</td>\n",
       "      <td>Barry French</td>\n",
       "      <td>293</td>\n",
       "      <td>457.81</td>\n",
       "      <td>208.16</td>\n",
       "      <td>68.02</td>\n",
       "      <td>Nunavut</td>\n",
       "      <td>Appliances</td>\n",
       "      <td>0.58</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   0                                                  1                   2  \\\n",
       "0  1   Eldon Base for stackable storage shelf, platinum  Muhammed MacIntyre   \n",
       "1  2  1.7 Cubic Foot Compact \"Cube\" Office Refrigera...        Barry French   \n",
       "\n",
       "     3       4       5      6        7                       8     9  \n",
       "0    3 -213.25   38.94  35.00  Nunavut  Storage & Organization  0.80  \n",
       "1  293  457.81  208.16  68.02  Nunavut              Appliances  0.58  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load library\n",
    "import pandas as pd\n",
    "\n",
    "# create url\n",
    "url = \"https://www.sample-videos.com/xls/Sample-Spreadsheet-10-rows.xls\"\n",
    "\n",
    "# load data\n",
    "df = pd.read_excel(url, sheet_name=0, header=None)\n",
    "\n",
    "# view the first two rows\n",
    "df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.5 Loading a JSON File\n",
    "#### Problem\n",
    "You need to load a JSON file for data preprocessing\n",
    "\n",
    "#### Solution\n",
    "The pandas library provides `read_json` to convert a JSON file a pandas object:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>archive_url</th>\n",
       "      <th>archived</th>\n",
       "      <th>assignees_url</th>\n",
       "      <th>blobs_url</th>\n",
       "      <th>branches_url</th>\n",
       "      <th>clone_url</th>\n",
       "      <th>collaborators_url</th>\n",
       "      <th>comments_url</th>\n",
       "      <th>commits_url</th>\n",
       "      <th>compare_url</th>\n",
       "      <th>...</th>\n",
       "      <th>subscribers_url</th>\n",
       "      <th>subscription_url</th>\n",
       "      <th>svn_url</th>\n",
       "      <th>tags_url</th>\n",
       "      <th>teams_url</th>\n",
       "      <th>trees_url</th>\n",
       "      <th>updated_at</th>\n",
       "      <th>url</th>\n",
       "      <th>watchers</th>\n",
       "      <th>watchers_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://api.github.com/repos/f00-/anscombes-qu...</td>\n",
       "      <td>False</td>\n",
       "      <td>https://api.github.com/repos/f00-/anscombes-qu...</td>\n",
       "      <td>https://api.github.com/repos/f00-/anscombes-qu...</td>\n",
       "      <td>https://api.github.com/repos/f00-/anscombes-qu...</td>\n",
       "      <td>https://github.com/f00-/anscombes-quartet.git</td>\n",
       "      <td>https://api.github.com/repos/f00-/anscombes-qu...</td>\n",
       "      <td>https://api.github.com/repos/f00-/anscombes-qu...</td>\n",
       "      <td>https://api.github.com/repos/f00-/anscombes-qu...</td>\n",
       "      <td>https://api.github.com/repos/f00-/anscombes-qu...</td>\n",
       "      <td>...</td>\n",
       "      <td>https://api.github.com/repos/f00-/anscombes-qu...</td>\n",
       "      <td>https://api.github.com/repos/f00-/anscombes-qu...</td>\n",
       "      <td>https://github.com/f00-/anscombes-quartet</td>\n",
       "      <td>https://api.github.com/repos/f00-/anscombes-qu...</td>\n",
       "      <td>https://api.github.com/repos/f00-/anscombes-qu...</td>\n",
       "      <td>https://api.github.com/repos/f00-/anscombes-qu...</td>\n",
       "      <td>2018-08-08 04:37:53</td>\n",
       "      <td>https://api.github.com/repos/f00-/anscombes-qu...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://api.github.com/repos/f00-/autograd-tut...</td>\n",
       "      <td>False</td>\n",
       "      <td>https://api.github.com/repos/f00-/autograd-tut...</td>\n",
       "      <td>https://api.github.com/repos/f00-/autograd-tut...</td>\n",
       "      <td>https://api.github.com/repos/f00-/autograd-tut...</td>\n",
       "      <td>https://github.com/f00-/autograd-tutorial.git</td>\n",
       "      <td>https://api.github.com/repos/f00-/autograd-tut...</td>\n",
       "      <td>https://api.github.com/repos/f00-/autograd-tut...</td>\n",
       "      <td>https://api.github.com/repos/f00-/autograd-tut...</td>\n",
       "      <td>https://api.github.com/repos/f00-/autograd-tut...</td>\n",
       "      <td>...</td>\n",
       "      <td>https://api.github.com/repos/f00-/autograd-tut...</td>\n",
       "      <td>https://api.github.com/repos/f00-/autograd-tut...</td>\n",
       "      <td>https://github.com/f00-/autograd-tutorial</td>\n",
       "      <td>https://api.github.com/repos/f00-/autograd-tut...</td>\n",
       "      <td>https://api.github.com/repos/f00-/autograd-tut...</td>\n",
       "      <td>https://api.github.com/repos/f00-/autograd-tut...</td>\n",
       "      <td>2018-06-06 03:17:19</td>\n",
       "      <td>https://api.github.com/repos/f00-/autograd-tut...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 72 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         archive_url  archived  \\\n",
       "0  https://api.github.com/repos/f00-/anscombes-qu...     False   \n",
       "1  https://api.github.com/repos/f00-/autograd-tut...     False   \n",
       "\n",
       "                                       assignees_url  \\\n",
       "0  https://api.github.com/repos/f00-/anscombes-qu...   \n",
       "1  https://api.github.com/repos/f00-/autograd-tut...   \n",
       "\n",
       "                                           blobs_url  \\\n",
       "0  https://api.github.com/repos/f00-/anscombes-qu...   \n",
       "1  https://api.github.com/repos/f00-/autograd-tut...   \n",
       "\n",
       "                                        branches_url  \\\n",
       "0  https://api.github.com/repos/f00-/anscombes-qu...   \n",
       "1  https://api.github.com/repos/f00-/autograd-tut...   \n",
       "\n",
       "                                       clone_url  \\\n",
       "0  https://github.com/f00-/anscombes-quartet.git   \n",
       "1  https://github.com/f00-/autograd-tutorial.git   \n",
       "\n",
       "                                   collaborators_url  \\\n",
       "0  https://api.github.com/repos/f00-/anscombes-qu...   \n",
       "1  https://api.github.com/repos/f00-/autograd-tut...   \n",
       "\n",
       "                                        comments_url  \\\n",
       "0  https://api.github.com/repos/f00-/anscombes-qu...   \n",
       "1  https://api.github.com/repos/f00-/autograd-tut...   \n",
       "\n",
       "                                         commits_url  \\\n",
       "0  https://api.github.com/repos/f00-/anscombes-qu...   \n",
       "1  https://api.github.com/repos/f00-/autograd-tut...   \n",
       "\n",
       "                                         compare_url       ...        \\\n",
       "0  https://api.github.com/repos/f00-/anscombes-qu...       ...         \n",
       "1  https://api.github.com/repos/f00-/autograd-tut...       ...         \n",
       "\n",
       "                                     subscribers_url  \\\n",
       "0  https://api.github.com/repos/f00-/anscombes-qu...   \n",
       "1  https://api.github.com/repos/f00-/autograd-tut...   \n",
       "\n",
       "                                    subscription_url  \\\n",
       "0  https://api.github.com/repos/f00-/anscombes-qu...   \n",
       "1  https://api.github.com/repos/f00-/autograd-tut...   \n",
       "\n",
       "                                     svn_url  \\\n",
       "0  https://github.com/f00-/anscombes-quartet   \n",
       "1  https://github.com/f00-/autograd-tutorial   \n",
       "\n",
       "                                            tags_url  \\\n",
       "0  https://api.github.com/repos/f00-/anscombes-qu...   \n",
       "1  https://api.github.com/repos/f00-/autograd-tut...   \n",
       "\n",
       "                                           teams_url  \\\n",
       "0  https://api.github.com/repos/f00-/anscombes-qu...   \n",
       "1  https://api.github.com/repos/f00-/autograd-tut...   \n",
       "\n",
       "                                           trees_url          updated_at  \\\n",
       "0  https://api.github.com/repos/f00-/anscombes-qu... 2018-08-08 04:37:53   \n",
       "1  https://api.github.com/repos/f00-/autograd-tut... 2018-06-06 03:17:19   \n",
       "\n",
       "                                                 url  watchers  watchers_count  \n",
       "0  https://api.github.com/repos/f00-/anscombes-qu...         0               0  \n",
       "1  https://api.github.com/repos/f00-/autograd-tut...         0               0  \n",
       "\n",
       "[2 rows x 72 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load library\n",
    "import pandas as pd\n",
    "\n",
    "# create url\n",
    "url = \"https://api.github.com/users/f00-/repos\"\n",
    "\n",
    "# load data\n",
    "df = pd.read_json(url, orient=\"columns\")\n",
    "\n",
    "# view first two rows\n",
    "df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.6 Querying a SQL Database\n",
    "#### Problem\n",
    "You need to load data from a databaseu sing structured query language (SQL)\n",
    "\n",
    "#### Solution\n",
    "`pandas`' `read_sql_query` allows us to make a SQL query to a database and load it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['albums', 'artists', 'customers', 'employees', 'genres', 'invoice_items', 'invoices', 'media_types', 'playlist_track', 'playlists', 'sqlite_sequence', 'sqlite_stat1', 'tracks']\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AlbumId</th>\n",
       "      <th>Title</th>\n",
       "      <th>ArtistId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>For Those About To Rock We Salute You</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Balls to the Wall</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Restless and Wild</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Let There Be Rock</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Big Ones</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   AlbumId                                  Title  ArtistId\n",
       "0        1  For Those About To Rock We Salute You         1\n",
       "1        2                      Balls to the Wall         2\n",
       "2        3                      Restless and Wild         2\n",
       "3        4                      Let There Be Rock         1\n",
       "4        5                               Big Ones         3"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load libraries\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "# create a connection to the database\n",
    "db_connection = create_engine('sqlite:///sample.db')\n",
    "\n",
    "# see table names\n",
    "print(db_connection.table_names())\n",
    "\n",
    "# load data\n",
    "df = pd.read_sql_query('SELECT * FROM albums', db_connection)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:machine_learning_cookbook]",
   "language": "python",
   "name": "conda-env-machine_learning_cookbook-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
